  //
//  DBHelper.m
//  HB
//
//  Created by 陶秋安 on 16/2/1.
//  Copyright © 2016年 孙智慧. All rights reserved.
//


#import "DBHelper.h"


DBHelper *db=nil;
static FMDatabase *fmdb;

@implementation DBHelper

#pragma mark 单例模式
+(DBHelper *)sharedMyClass
{
    @synchronized(self){  //为了确保多线程情况下，仍然确保实体的唯一性
        
        if (!db) {
            db= [[self alloc] init]; //该方法会调用 allocWithZone
        }
    }
    return db;
}

+(id)allocWithZone:(NSZone *)zone{
    
    @synchronized(self){
        
        if (!db) {
            
            db = [super allocWithZone:zone]; //确保使用同一块内存地址
            
            return db;
            
        }
        
    }
    
    return nil;
}

- (id)copyWithZone:(NSZone *)zone;{
    return self; //确保copy对象也是唯一
}



//创建数据库
-(void) createDB :(NSString *) database_path
{
    fmdb = [FMDatabase databaseWithPath:database_path];
}

//创建表
-(void) createTable
{
    //用户信息
    if ([fmdb open])
    {
        NSString *sqlCreateTable = @"CREATE TABLE IF NOT EXISTS 'DJInfo' ('djID' TEXT PRIMARY KEY, 'nickName' TEXT, 'headImgUrl' TEXT, 'headImgName' TEXT,'login' BOOLEAN,'loginDate' DATETIME,'code' INTERGER,'VipID' INTERGER,'VipExpiredTime' DATETIME,'QualityID' INTERGER)";
        BOOL res = [fmdb executeUpdate:sqlCreateTable];
        if (!res) {
            
        } else {
             
        }
        
        
//        //缓存歌曲
//        NSString *sqlCreateTable1 = @"CREATE TABLE IF NOT EXISTS 'CatchMusic' ('musicID' TEXT PRIMARY KEY, 'musicName' TEXT, 'icon' TEXT, 'iconName' TEXT,'singer' TEXT,'addDate' DATETIME,'locationPath' TEXT,'userID' TEXT)";
//        BOOL res1 = [fmdb executeUpdate:sqlCreateTable1];
//        if (!res1) {
//            
//        } else {
//            
//        }
        
        
        //最近播放
        NSString *sqlCreateTable2 = @"CREATE TABLE IF NOT EXISTS 'ViewResourse' ('musicID' TEXT PRIMARY KEY, 'musicName' TEXT, 'icon' TEXT, 'iconName' TEXT,'singer' TEXT,'addDate' DATETIME,'djID' TEXT,'shareURL' TEXT)";
        BOOL res2 = [fmdb executeUpdate:sqlCreateTable2];
        if (!res2)
        {
            
        }
        else
        {
            
        }
        
        //最近播放
        NSString *sqlCreateTable3 = @"CREATE TABLE IF NOT EXISTS 'ViewResourseQuality' (ID integer PRIMARY KEY autoincrement,'musicID' TEXT, 'QualityName' TEXT, 'FileName' TEXT, 'Path' TEXT,'Point' TEXT,'Priority' INTERGER,'Size' INTERGER)";
        BOOL res3 = [fmdb executeUpdate:sqlCreateTable3];
        if (!res3)
        {
            
        }
        else
        {
            
        }
        
//        //我的碟包
//        NSString *sqlCreateTable3 = @"CREATE TABLE IF NOT EXISTS 'MyPakage' ('musicID' TEXT PRIMARY KEY, 'musicName' TEXT, 'icon' TEXT, 'iconName' TEXT,'singer' TEXT,'addDate' DATETIME,'userID' TEXT)";
//        
//        BOOL res3 = [fmdb executeUpdate:sqlCreateTable3];
//        
//        if (!res3) {
//            
//        } else {
//            
//        }
        
//        //我的收藏
//        
//        NSString *sqlCreateTable4 = @"CREATE TABLE IF NOT EXISTS 'MyCollection' ('musicID' TEXT PRIMARY KEY, 'musicName' TEXT, 'icon' TEXT, 'iconName' TEXT,'singer' TEXT,'addDate' DATETIME,'userID' TEXT)";
//        BOOL res4 = [fmdb executeUpdate:sqlCreateTable4];
//        if (!res4) {
//            
//        } else {
//            
//        }
        [fmdb close];
        
    }
    
}

#pragma 用户表操作 ------------------------- begin -----------------
-(void) addData:(UserInfo *)userInfo
{
    if ([fmdb open])
    {
        BOOL hasRow = NO;
        
        FMResultSet * rs22 =[fmdb executeQuery:@"select * from DJInfo where djID = ?",[NSString stringWithFormat:@"%@",userInfo.djID]];
        
        while([rs22 next])
        {
            hasRow=YES;
        }
        
        
        [rs22 close];
        
        if (!hasRow)
        {
            NSString *insertSql1= [NSString stringWithFormat:
                                   @"INSERT INTO 'DJInfo' ('djID','nickName', 'headImgUrl', 'headImgName','login','loginDate','code','VipID','VipExpiredTime','QualityID') VALUES ('%@','%@','%@','%@',%d,'%@',%ld,%ld,'%@',%ld)",
                                   userInfo.djID,userInfo.nickName,userInfo.headImgUrl,userInfo.headImgName,userInfo.login,userInfo.loginDate,(long)userInfo.code,userInfo.VipID,userInfo.VipExpiredTime,userInfo.QualityID];
            
            BOOL res = [fmdb executeUpdate:insertSql1];
            
            if (!res)
            {
                
            }
            else
            {
                
            }
        }
        else
        {
            [self upData:userInfo.djID andIsLogin:YES];
        }
        
        [fmdb close];
    }
}

-(void) upData:(NSString *)djID andIsLogin:(BOOL)isLogin
{
    if ([fmdb open]) {
        NSString *updateSql = [NSString stringWithFormat:
                               @"UPDATE 'DJInfo' SET 'login' = %d WHERE djID = '%@'",
                               isLogin,djID];
        BOOL res = [fmdb executeUpdate:updateSql];
        if (!res) {
            NSLog(@"error when update db table");
        } else {
            NSLog(@"success to update db table");
        }
        [fmdb close];
    }
}

-(void) delData:(NSString *)djID
{
    if ([fmdb open]) {
        
        NSString *deleteSql = [NSString stringWithFormat:
                               @"delete from DJInfo where djID = '%@'",djID];
        BOOL res = [fmdb executeUpdate:deleteSql];
        
        if (!res) {
            NSLog(@"error when delete db table");
        } else {
            NSLog(@"success to delete db table");
        }
        [fmdb close];
        
    }
}

-(NSMutableDictionary *) queryData
{
 
    NSMutableDictionary *dict=[[NSMutableDictionary alloc]init];
    if ([fmdb open]) {
        NSString * sql = [NSString stringWithFormat:
                          @"SELECT * FROM %@",@"DJInfo"];
        FMResultSet * rs = [fmdb executeQuery:sql];
        while ([rs next]) {
            
            NSString * djID = [rs stringForColumn:@"djID"];
            [dict setObject:djID forKey:@"djID"];
            
            NSString * nickName = [rs stringForColumn:@"nickName"];
            [dict setObject:nickName forKey:@"nickName"];
            
            NSString * headImgName = [rs stringForColumn:@"headImgName"];
            [dict setObject:headImgName forKey:@"headImgName"];
            
            NSString * headImgUrl = [rs stringForColumn:@"headImgUrl"];
            [dict setObject:headImgUrl forKey:@"headImgUrl"];
            
            NSString * loginDate = [rs stringForColumn:@"loginDate"];
            [dict setObject:loginDate forKey:@"loginDate"];
            
            NSString * islogin = [rs stringForColumn:@"login"];
            [dict setObject:islogin forKey:@"login"];
            
            NSString * code = [rs stringForColumn:@"code"];
            [dict setObject:code forKey:@"code"];
            
        }
        [rs close];
        [fmdb close];
    }
    return dict;
}
#pragma 用户表操作 ------------------------- end -----------------

#pragma 缓存歌曲操作 ------------------------- begin -----------------
//-(void) addCatchMusic:(CatchMusic *)catchMusic
//{
//    if ([fmdb open]) {
//        NSString *insertSql1= [NSString stringWithFormat:
//                               @"INSERT INTO 'CatchMusic' ('musicID','musicName', 'icon', 'iconName','singer','addDate','locationPath','userID') VALUES ('%@','%@','%@','%@','%@', '%@','%@','%@')",
//                               catchMusic.musicID,catchMusic.musicName,catchMusic.icon,catchMusic.iconName,catchMusic.singer,[NSDate date],catchMusic.locationPath,catchMusic.userID];
//        BOOL res = [fmdb executeUpdate:insertSql1];
//        if (!res) {
//            NSLog(@"error when insert CatchMusic table");
//        } else {
//            NSLog(@"success to insert CatchMusic table");
//        }
//        [fmdb close];
//    }
//}
//-(NSMutableArray *) getCatchMusic
//{
//    NSMutableArray *result=[[NSMutableArray alloc] init];
//    if ([fmdb open]) {
//        NSString * sql = [NSString stringWithFormat:
//                          @"SELECT * FROM %@",@"CatchMusic"];
//        FMResultSet * rs = [fmdb executeQuery:sql];
//        while ([rs next]) {
//            NSMutableDictionary *dict=[[NSMutableDictionary alloc]init];
//            NSString * musicID = [rs stringForColumn:@"musicID"];
//            [dict setObject:musicID forKey:@"musicID"];
//            
//            NSString * musicName = [rs stringForColumn:@"musicName"];
//            [dict setObject:musicName forKey:@"musicName"];
//            
//            NSString * icon = [rs stringForColumn:@"icon"];
//            [dict setObject:icon forKey:@"icon"];
//            
//            NSString * iconName = [rs stringForColumn:@"iconName"];
//            [dict setObject:iconName forKey:@"iconName"];
//            
//            NSString * singer = [rs stringForColumn:@"singer"];
//            [dict setObject:singer forKey:@"singer"];
//            
//            NSString * addDate = [rs stringForColumn:@"addDate"];
//            [dict setObject:addDate forKey:@"addDate"];
//            
//            NSString * locationPath = [rs stringForColumn:@"locationPath"];
//            [dict setObject:locationPath forKey:@"locationPath"];
//            
//            NSString * userID = [rs stringForColumn:@"userID"];
//            [dict setObject:userID forKey:@"userID"];
//            [result addObject:dict];
//        }
//        [fmdb close];
//    }
//    return result;
//}
#pragma 缓存歌曲操作 ------------------------- end -----------------

#pragma 最近播放操作 ------------------------- begin -----------------
-(void) addViewResourse:(ZYMusic *)music
{
    BOOL hasRow=NO;
    if ([fmdb open])
    {
        FMResultSet * rs22 =[fmdb executeQuery:@"select * from ViewResourse where musicID = ?",[NSString stringWithFormat:@"%@",music.musicID]];
        
        while([rs22 next])
        {
            hasRow=YES;
        }
        
        [rs22 close];
        
        [fmdb close];
    }
    
    if(hasRow)
    {
        [fmdb open];
        //修改
        NSString *updateSql=[NSString stringWithFormat:@"update 'ViewResourse' set 'addDate'='%@' where 'musicID'='%@'",[NSDate date],music.musicID];
        
        BOOL res1 = [fmdb executeUpdate:updateSql];
        
        if (!res1)
        {
            
        }
        else
        {
        
        }
        
        [fmdb close];
    }
    else
    {
        [fmdb open];
        
        //添加
        NSString *insertSql1= [NSString stringWithFormat:
                               @"INSERT INTO 'ViewResourse' ('musicID', 'musicName','icon', 'iconName', 'singer', 'addDate', 'djID','shareURL') VALUES ('%@','%@','%@','%@','%@','%@','%@','%@')",
                               music.musicID, music.name, music.icon,music.iconName,music.singer,[NSDate date],music.djID,music.shareURL];
        
        BOOL res1 = [fmdb executeUpdate:insertSql1];
        
        if (!res1)
        {
 
        }
        else
        {
 
        }
 
        for (NSInteger i=0; i<[music.musicListArray count]; i++)
        {
            NSDictionary *dict= [music.musicListArray objectAtIndex:i];
 
            //添加
            NSString *insertSql2= [NSString stringWithFormat:
                                   @"INSERT INTO 'ViewResourseQuality' ('musicID','QualityName','FileName','Path', 'Point','Priority','Size') VALUES ('%@','%@','%@','%@','%@',%@,%@)",
                                   music.musicID,[dict objectForKey:@"QualityName"],[dict objectForKey:@"FileName"],[dict objectForKey:@"FilePath"],[dict objectForKey:@"Point"],[dict objectForKey:@"Priority"],[dict objectForKey:@"Size"]];
 
            
            BOOL res1 = [fmdb executeUpdate:insertSql2];
            
            if (!res1)
            {
                
            }
            else
            {
                
            }

        }
        [fmdb close];
    }

}

-(NSArray *) getViewResourse
{
    NSMutableArray *result=[[NSMutableArray alloc] init];
    if ([fmdb open])
    {
        NSString * sql = [NSString stringWithFormat:
                          @"SELECT * FROM %@",@"ViewResourse"];
        FMResultSet * rs = [fmdb executeQuery:sql];
        while ([rs next])
        {
            NSMutableDictionary *dict=[[NSMutableDictionary alloc]init];
            NSString * musicID = [rs stringForColumn:@"musicID"];
            [dict setObject:musicID forKey:@"musicID"];
            
            NSString * musicName = [rs stringForColumn:@"musicName"];
            [dict setObject:musicName forKey:@"Title"];
            
            NSString * icon = [rs stringForColumn:@"icon"];
            [dict setObject:icon forKey:@"icon"];
            
            NSString * iconName = [rs stringForColumn:@"iconName"];
            [dict setObject:iconName forKey:@"iconName"];
            
            NSString * singer = [rs stringForColumn:@"singer"];
            [dict setObject:singer forKey:@"singer"];
             
            NSString * djID = [rs stringForColumn:@"djID"];
            [dict setObject:djID forKey:@"djID"];
            
            NSString * shareURL = [rs stringForColumn:@"shareURL"];
            if(shareURL==nil)
            {
                shareURL=@"";
            }
            [dict setObject:shareURL forKey:@"shareURL"];
 
            NSString * sql1 = [NSString stringWithFormat:
                              @"SELECT * FROM %@ where musicID='%@'",@" ViewResourseQuality ",musicID];
            FMResultSet * rs1 = [fmdb executeQuery:sql1];
            
            NSMutableArray *musicListArray=[[NSMutableArray alloc] init];
            
            while ([rs1 next])
            {
                NSMutableDictionary *dict1=[[NSMutableDictionary alloc]init];
                
                /*
                  music.musicID,[dict objectForKey:@"QualityName"],[dict objectForKey:@"FileName"],[dict objectForKey:@"FilePath"],[dict objectForKey:@"Point"],[dict objectForKey:@"Priority"],[dict objectForKey:@"Size"]];
                 */
                [dict1 setObject:[rs1 stringForColumn:@"musicID"] forKey:@"musicID"];
                [dict1 setObject:[rs1 stringForColumn:@"QualityName"] forKey:@"QualityName"];
                [dict1 setObject:[rs1 stringForColumn:@"FileName"] forKey:@"FileName"];
                [dict1 setObject:[rs1 stringForColumn:@"Path"] forKey:@"FilePath"];
                [dict1 setObject:[rs1 stringForColumn:@"Point"] forKey:@"Point"];
                [dict1 setObject:[rs1 stringForColumn:@"Priority"] forKey:@"Priority"];
                [dict1 setObject:[rs1 stringForColumn:@"Size"] forKey:@"Size"];
                
                [musicListArray addObject:dict1];
                
            }
            [dict setObject:musicListArray forKey:@"musiclist"];
            [result addObject:dict];
            [rs1 close];
            
        }
        [rs close];
        [fmdb close];
    }
    return result;
}

#pragma 最近播放操作 ------------------------- end -----------------

#pragma 我的收藏操作 ------------------------- begin -----------------
//-(void) addMyCollection:(MyCollection *)myCollection
//{
//    if ([fmdb open]) {
//        NSString *insertSql1= [NSString stringWithFormat:
//                               @"INSERT INTO 'MyCollection' ('musicID','musicName', 'icon', 'iconName','singer','addDate','userID') VALUES ('%@','%@','%@','%@','%@', '%@','%@')",
//                               myCollection.musicID,myCollection.musicName,myCollection.icon,myCollection.iconName,myCollection.singer,[NSDate date],myCollection.userID];
//        BOOL res = [fmdb executeUpdate:insertSql1];
//        if (!res) {
//            NSLog(@"error when insert myCollection table");
//        } else {
//            NSLog(@"success to insert myCollection table");
//        }
//        [fmdb close];
//    }
//}
//
//-(NSMutableArray *) getMyCollection
//{
//    NSMutableArray *result=[[NSMutableArray alloc] init];
//    if ([fmdb open]) {
//        NSString * sql = [NSString stringWithFormat:
//                          @"SELECT * FROM %@",@"MyCollection"];
//        FMResultSet * rs = [fmdb executeQuery:sql];
//        while ([rs next]) {
//            NSMutableDictionary *dict=[[NSMutableDictionary alloc]init];
//            
//            NSString * musicID = [rs stringForColumn:@"musicID"];
//            [dict setObject:musicID forKey:@"musicID"];
//            
//            NSString * musicName = [rs stringForColumn:@"musicName"];
//            [dict setObject:musicName forKey:@"musicName"];
//            
//            NSString * icon = [rs stringForColumn:@"icon"];
//            [dict setObject:icon forKey:@"icon"];
//            
//            NSString * iconName = [rs stringForColumn:@"iconName"];
//            [dict setObject:iconName forKey:@"iconName"];
//            
//            NSString * singer = [rs stringForColumn:@"singer"];
//            [dict setObject:singer forKey:@"singer"];
//            
//            NSString * addDate = [rs stringForColumn:@"addDate"];
//            [dict setObject:addDate forKey:@"addDate"];
//            
//            
//            NSString * userID = [rs stringForColumn:@"userID"];
//            [dict setObject:userID forKey:@"userID"];
//            
//            [result addObject:dict];
//            
//        }
//        [fmdb close];
//    }
//    return result;
//    
//}
#pragma 我的收藏操作 ------------------------- end -----------------

#pragma 我的碟包操作 ------------------------- begin -----------------
//-(void) addMyPakage:(MyPakage *)myPakage
//{
//    if ([fmdb open]) {
//        NSString *insertSql1= [NSString stringWithFormat:
//                               @"INSERT INTO 'MyPakage' ('musicID','musicName', 'icon', 'iconName','singer','addDate','userID') VALUES ('%@','%@','%@','%@','%@', '%@','%@')",
//                               myPakage.musicID,myPakage.musicName,myPakage.icon,myPakage.iconName,myPakage.singer,[NSDate date],myPakage.userID];
//        BOOL res = [fmdb executeUpdate:insertSql1];
//        if (!res) {
//            NSLog(@"error when insert myPakage table");
//        } else {
//            NSLog(@"success to insert myPakage table");
//        }
//        [fmdb close];
//    }
//}
//
//-(NSMutableArray *) getMyPakage
//{
//    
//    NSMutableArray *result=[[NSMutableArray alloc] init];
//    
//    if ([fmdb open]) {
//        NSString * sql = [NSString stringWithFormat:
//                          @"SELECT * FROM %@",@"MyPakage"];
//        FMResultSet * rs = [fmdb executeQuery:sql];
//        while ([rs next]) {
//            NSMutableDictionary *dict=[[NSMutableDictionary alloc]init];
//            NSString * musicID = [rs stringForColumn:@"musicID"];
//            [dict setObject:musicID forKey:@"musicID"];
//            
//            NSString * musicName = [rs stringForColumn:@"musicName"];
//            [dict setObject:musicName forKey:@"musicName"];
//            
//            NSString * icon = [rs stringForColumn:@"icon"];
//            [dict setObject:icon forKey:@"icon"];
//            
//            NSString * iconName = [rs stringForColumn:@"iconName"];
//            [dict setObject:iconName forKey:@"iconName"];
//            
//            NSString * singer = [rs stringForColumn:@"singer"];
//            [dict setObject:singer forKey:@"singer"];
//            
//            NSString * addDate = [rs stringForColumn:@"addDate"];
//            [dict setObject:addDate forKey:@"addDate"];
//            
//            
//            NSString * userID = [rs stringForColumn:@"userID"];
//            [dict setObject:userID forKey:@"userID"];
//            
//            [result addObject:dict];
//        }
//        [fmdb close];
//    }
//    return result;
//    
//}
#pragma 我的碟包操作 ------------------------- end -----------------

@end
